﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;


/// <summary>
/// Summary description for ManageBusRides
/// </summary>
public class ManageBus
{
    public ManageBus()
    {
    }

    /// <summary>
    /// Lấy danh sách tất cả các tài xế
    /// </summary>
    /// <returns></returns>
    public DataSet getAllBus()
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("getAllBuss", con.ConnectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "Drivers");
        }
        catch { }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }


    /// <summary>
    /// tìm kiếm tất cả các xe bus theo số đăng ký
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    public DataSet searchBusByKeyword(string key)
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter sqlDb = new SqlDataAdapter("searchBussByKey", con);
            sqlDb.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@STRING_SEARCH", SqlDbType.NVarChar);
            param.Value = key;
            sqlDb.SelectCommand.Parameters.Add(param);

            sqlDb.Fill(ds, "BusRides");
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/

        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    /// <summary>
    /// thêm mới 1 chiếc xe bus
    /// </summary>
    /// <param name="bus"></param>
    /// <returns></returns>
    public int insertNewBus(Bus bus)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("insertNewBus", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@RegistrationNumber", SqlDbType.NVarChar).Value = bus.RegistrationNumber;
        cmd.Parameters.Add("@NumberMachine", SqlDbType.NVarChar).Value = bus.NumberMachine;
        cmd.Parameters.Add("@NumberChassis", SqlDbType.NVarChar).Value = bus.NumberChassis;
        cmd.Parameters.Add("@Capacity", SqlDbType.Int).Value = bus.Capacity;
        cmd.Parameters.Add("@StatusBus", SqlDbType.NVarChar).Value = bus.StatusBus;
        cmd.Parameters.Add("@StartDateUse", SqlDbType.Date).Value = bus.StartDateUse;
        cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = bus.Note;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    /// <summary>
    /// hàm chỉnh sửa thông tin của 1 chiếc xe bus
    /// </summary>
    /// <param name="bus">đối tượng muốn chỉnh sửa</param>
    /// <param name="updateRegisterNumber"></param>
    /// <returns></returns>
    public int updateBus(Bus bus)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("updateBusByID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDBus", SqlDbType.Int).Value = bus.IDBus;
        cmd.Parameters.Add("@RegistrationNumber", SqlDbType.NVarChar).Value = bus.RegistrationNumber;
        cmd.Parameters.Add("@NumberMachine", SqlDbType.NVarChar).Value = bus.NumberMachine;
        cmd.Parameters.Add("@NumberChassis", SqlDbType.NVarChar).Value = bus.NumberChassis;
        cmd.Parameters.Add("@Capacity", SqlDbType.Int).Value = bus.Capacity;
        cmd.Parameters.Add("@StatusBus", SqlDbType.NVarChar).Value = bus.StatusBus;
        cmd.Parameters.Add("@StartDateUse", SqlDbType.Date).Value = bus.StartDateUse;
        cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = bus.Note;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    /// <summary>
    /// hàm xóa 1 bus với đầu vào là 1 đối tượng bus muốn xóa
    /// </summary>
    /// <param name="bus"></param>
    /// <returns></returns>
    public int deleteBus(Bus bus)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("deleteBusByRegisterNumber", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDBUS", SqlDbType.Int).Value = bus.IDBus;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }
}